﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Xml.Linq;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace LUStationery.DAO
{
    public class OrderDAO
    {
        SqlConnection cn;
        SqlCommand cmd;
        public OrderDAO()
        {
            cn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
        }
        //ZHONG YUE\\
        public void addOrder
            (int supplierItemID, string itemCode, string itemDesc, string supplierCode,
            string supplierName, int orderQty, double price,
            double orderPrice, DateTime OrderDate, string status)
        {
            cmd = new SqlCommand("insert into orders (supplier_item_id,item_code,item_description,supplier_code,supplier_name,order_qty,price, order_price,order_date,order_status)values(@supplierItemID,@itemCode,@itemDesc,@supplierCode,@supplierName,@orderQty,@price,@orderPrice,@OrderDate,@status)", cn);
            cmd.Parameters.Add("@supplierItemID", SqlDbType.VarChar).Value = supplierItemID;
            cmd.Parameters.Add("@itemCode", SqlDbType.VarChar).Value = itemCode;
            cmd.Parameters.Add("@itemDesc", SqlDbType.VarChar).Value = itemDesc;
            cmd.Parameters.Add("@supplierCode", SqlDbType.VarChar).Value = supplierCode;
            cmd.Parameters.Add("@supplierName", SqlDbType.VarChar).Value = supplierName;
            cmd.Parameters.Add("@orderQty", SqlDbType.VarChar).Value = orderQty;
            cmd.Parameters.Add("@price", SqlDbType.VarChar).Value = price;
            cmd.Parameters.Add("@orderPrice", SqlDbType.VarChar).Value = orderPrice;
            cmd.Parameters.Add("@OrderDate", SqlDbType.VarChar).Value = OrderDate;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cn.Open();
            cmd.ExecuteNonQuery();
        }

        //ZHONG YUE\\
        public IDataReader getOrdersBySupplierAndStatus(string supplier, string status)
        {
            cmd = new SqlCommand("select * from orders where supplier_name=@supplier and order_status=@status", cn);
            //cmd = new SqlCommand("select order_id,item_code,item_description,order_qty,price,order_price,order_date,order_status from orders where supplier_name=@supplier and order_status=@status", cn);
            cmd.Parameters.Add("@supplier", SqlDbType.VarChar).Value = supplier;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        //ZHONG YUE\\
        public IDataReader getOrderByOrderID(int orderID)
        {
            cmd = new SqlCommand("select * from orders where order_id=@orderID", cn);
            cmd.Parameters.Add("@orderID", SqlDbType.VarChar).Value = orderID;
            cn.Open();
            IDataReader idr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return idr;
        }

        //ZHONG YUE\\
        public void addOrderToPo(int poID, int orderID)
        {
            cmd = new SqlCommand("update orders set purchase_order_id=@purchaseOrderID where order_id=@orderID", cn);
            cmd.Parameters.Add("@orderID", SqlDbType.VarChar).Value = orderID;
            cmd.Parameters.Add("@purchaseOrderID", SqlDbType.VarChar).Value = poID;
            cn.Open();
            cmd.ExecuteNonQuery();
        }

        public void updateOrderStatus(int poID, string status)
        {
            cmd = new SqlCommand("update orders set order_status=@status where purchase_order_id=@purchaseOrderID ", cn);
            cmd.Parameters.Add("@purchase_order_id", SqlDbType.VarChar).Value = poID;
            cmd.Parameters.Add("@status", SqlDbType.VarChar).Value = status;
            cmd.Parameters.Add("@purchaseOrderID", SqlDbType.VarChar).Value = poID;
            cn.Open();
            cmd.ExecuteNonQuery();
        }
    }
}
